<?php

namespace app\admin\controller\school;

use app\admin\library\Auth;
use app\common\controller\Backend;


use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use think\exception\PDOException;
use ZipArchive;

/**
 * 学员信息管理
 *
 * @icon fa fa-circle-o
 */
class Student extends Backend
{

    /**
     * Student模型对象
     * @var \app\admin\model\school\Student
     */
    protected $model = null;
    protected $selectpageReturnFull = true;
    protected $selectpageFields = "*";


    protected $importMapAttrs = ['student_sex', 'status'];


    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\school\Student();
        $this->model->relation("major");
        $this->model->with("major");
        $this->view->assign("workStationDictList", $this->model->getWorkStationDictList());
        $this->assignconfig("workStationDictList", $this->model->getWorkStationDictList());
        $this->view->assign("majorLevelDictList", $this->model->getMajorLevelDictList());
        $this->assignconfig("majorLevelDictList", $this->model->getMajorLevelDictList());
        $this->view->assign("studentTypeDictList", $this->model->getStudentTypeDictList());
        $this->assignconfig("studentTypeDictList", $this->model->getStudentTypeDictList());
        $this->view->assign("studentSexList", $this->model->getStudentSexList());
        $this->view->assign("studentPoliticalStatusDictList", $this->model->getStudentPoliticalStatusDictList());
        $this->assignconfig("studentPoliticalStatusDictList", $this->model->getStudentPoliticalStatusDictList());
        $this->view->assign("statusList", $this->model->getStatusList());
        $this->view->assign("studentLastLevelDictList", $this->model->getStudentLastLevelDictList());
        $this->assignconfig("studentLastLevelDictList", $this->model->getStudentLastLevelDictList());
        $this->view->assign("studentLastTypeDictList", $this->model->getStudentLastTypeDictList());
        $this->assignconfig("studentLastTypeDictList", $this->model->getStudentLastTypeDictList());

        $this->zipService = new \app\admin\library\MakeZip();
    }

    /**
     * 默认生成的控制器所继承的父类中有index/add/edit/del/multi五个基础方法、destroy/restore/recyclebin三个回收站方法
     * 因此在当前控制器中可不用编写增删改查的代码,除非需要自己控制这部分逻辑
     * 需要将application/admin/library/traits/Backend.php中对应的方法复制到当前控制器,然后进行修改
     */

    /**
     * 导入
     */
    public function import()
    {
        parent::import();
    }

    //搜索框快速搜索的字段
    protected $searchFields = 'name,student_number,student_tel,major_id_text';

    



    // public function zip_download($path, $filename)
    // {
    //     $path = ['/1.xls', '/2.xls'];
    //     // 最终生成的文件名（含路径）
    //     $filename = '../gsr.zip';

    //     // 如果存在压缩文件，删除
    //     if (file_exists($filename)) {
    //         unlink($filename);
    //     }
    //     //重新生成文件
    //     $zip = new ZipArchive();
    //     if ($zip->open($filename, \ZipArchive::OVERWRITE | \ZipArchive::CREATE) !== TRUE) {
    //         exit('无法打开文件，或者文件创建失败');
    //         return 0;
    //     } else {
    //         exit('创建成功');
    //     }
    //     // 要压缩的文件
    //     $datalist = $path;
    //     // 遍历添加待压缩文件
    //     foreach ($datalist as $val) {
    //         $p = '../exportfile' . $val;
    //         if (file_exists($p)) {
    //             //val:qrcode/1_about.png
    //             $zip->addFile($val);
    //         } else {
    //             exit('不存在');
    //         }
    //     }
    //     // // 关闭
    //     $zip->close();
    //     // // if (!file_exists($filename)) {
    //     // //     // 即使创建，仍有可能失败
    //     // //     exit('无法找到文件');
    //     // //     return 0;
    //     // // }
    //     // header('Content-type: application/zip');
    //     // header('Content-Disposition: attachment; filename="qrcode_download.zip"');
    //     // readfile($filename);
    //     // return 1;
    // }

    public function exportStudent($ids = "")
    {
        
      
        // $ids = [7]; //将id放入数字中
        // $ids = [7,8]; //将id放入数字中
        $ids = explode(',', $ids);//获取到了分好的数组
        // print_r($ids);
        $fileListName = [];//存放多学院导出文件的文件名
        if (count($ids) == 1) {
            //但学员导出页面excel
            $this->exportStudent1($ids[0], 'single');
        }
        if (count($ids) > 1) {
            // 多学院导出学籍卡
            $i = 0;
            foreach ($ids as $stu) {
                $listname = $this->exportStudent1($stu, 'more');
                array_push($fileListName,$listname);
                $i++;
                if($i== (count($ids)-1)){
                break;
                }
            }
             // $fileListName = ['1.xls', '2.xls'];
            $createZipName = 'export'.date("Y-m-d") .' '.rand(1000, 9999);
            $this->zipService->zipfile($createZipName, $fileListName);
        }
       
    }

    // //学籍卡导出
    public function exportStudent1($ids, $type)
    {

        //1、读取前台点击导出的id
        //2、根据学员id，查询出该学员信息、专业信息、成绩信息

        $stu = $this->model->getStudentByIds($ids);
        if(empty($stu)){//如果没有查出对应的学员信息
            die();
        }
        $name = $stu['name'];
        $student_number = $stu['student_number'];
        $work_station_dict = $stu['work_station_dict'];
        $exametime = $stu['exametime'];

        $spreadsheet = new Spreadsheet(); //创建一个sheet页
        $sheet = $spreadsheet->getActiveSheet(); //获取一个可以操作的sheet页
        $this->setColumn($sheet);
        //设置sheet的名字  两种方法
        $sheet->setTitle('正面'); //设置一张sheet页的名字

        // $spreadsheet->getActiveSheet()->setTitle('Hello');
        //设置第一行小标题
        $k = 1; //第一行
        $sheet->mergeCells('A1:AD1'); //第一行合并单元格
        $sheet->mergeCells('A2:AD2'); //第二行合并单元格
        //设置几到几单元格的格式
        $sheet->getStyle('A1:AD2')->applyFromArray([
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER, //水平居中
                'vertical' => Alignment::VERTICAL_CENTER, //上下居中
            ],
            'font' => [
                'name' => '黑体',
                'bold' => true,
                'size' => 16
            ]
        ]);
        $sheet->setCellValueByColumnAndRow(1, 1, '北京印刷学院继续教育学院'); //大单元格第1列，第1行设置内容
        $sheet->setCellValueByColumnAndRow(1, 2, '学生学籍、成绩登记表'); //大单元格第1列，第二行设置内容
        //第三行空
        $sheet->getRowDimension('3')->setRowHeight(1);
        //第四行
        $sheet->mergeCells('A4:AD4');
        $sheet->getStyle('A4:AD4')->applyFromArray([
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER, //水平居中
                'vertical' => Alignment::VERTICAL_CENTER, //上下居中
            ],
            'font' => [
                'name' => '仿宋_GB2312',
                'bold' => true,
                'size' => 10.5
            ]
        ]);
        $sheet->setCellValueByColumnAndRow(1, 4, '工作站：' . $stu['work_station_dict_text'] . ' 成考录取年：' . $stu['exametime'] . ' 培养层次：' . $stu['major_level_dict_text'] . ' 专业：' . $stu['major_id_text'] . ' 学习形式: ' . $stu['student_type_dict_text'] . ' 身份证号：' . $stu['student_id_code'] . ' 姓名：' . $stu['name']);
        //正文加粗字体
        $font1 =  [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER, //水平居中
                'vertical' => Alignment::VERTICAL_CENTER, //上下居中
            ],
            'font' => [
                'name' => '仿宋_GB2312',
                'bold' => true,
                'size' => 10.5,
            ],
            'borders' => [ //设置边框
                'outline' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => '000000']
                ],
            ]
        ];
        //正文普通字体
        $font2 =  [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER, //水平居中
                'vertical' => Alignment::VERTICAL_CENTER, //上下居中
            ],
            'font' => [
                'name' => '仿宋_GB2312',
                'size' => 10.5,
            ],
            'borders' => [ //设置边框
                'outline' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => '000000']
                ],
            ]
        ];

        //正文普通小字体
        $font3 =  [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER, //水平居中
                'vertical' => Alignment::VERTICAL_CENTER, //上下居中
            ],
            'font' => [
                'name' => '仿宋_GB2312',
                'size' => 9,
            ],
            'borders' => [ //设置边框
                'outline' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => '000000']
                ],
            ]
        ];
        $exportData = [
            'A5:D8' => [['2', ''], '照片'], //照片黏贴处
            'E5:H5' => [['1', ''], '姓名'],
            'I5:N5' => [['2', ''], $stu['name']],
            'O5:S5' => [['1', ''], '性 别'],
            'T5' => [['2', ''], $stu['student_sex_text']],
            'E6:H6' => [['1', ''], '出生日期'],
            'I6:N6' => [['2', ''], explode(' ', $stu['student_birthday'])[0]],
            'O6:S6' => [['1', ''], '民 族'],
            'T6' => [['2', ''], $stu['student_nation']],
            'E7:H7' => [['1', ''], '出生地'],
            'I7:N7' => [['2', ''], $stu['student_birth_localtion']],
            'O7:S7' => [['1', ''], '政治面貌'],
            'T7' => [['2', ''], $stu['student_political_status_dict_text']],
            'E8:H8' => [['2', ''], '学  号'],
            'I8:T8' => [['2', ''], $stu['student_number']],
            'A9:D9' => [['1', ''], '通讯地址'],

            'B12:D12' => [['2', ''], '工作单位'],
            'E12:T12' => [['2', ''], $stu['student_work_unit']],
            'B13:D13' => [['2', ''], '单位电话'],
            'E13:T13' => [['2', ''], $stu['student_work_unit_tel']],
            'A14:A16' => [['2', 'warpText'], '入学成绩'],
            'B14:C15' => [['2', ''], '政治'],
            'B16:C16' => [['2', ''], $stu['scoure_politics']],
            'D14:E15' => [['2', ''], '语文'],
            'D16:E16' => [['2', ''], $stu['scoure_chinese']],
            'F14:G15' => [['2', ''], '数学'],
            'F16:G16' => [['2', ''], $stu['scoure_math']],
            'H14:K15' => [['2', ''], '外语'],
            'H16:K16' => [['2', ''], $stu['scoure_english']],
            'L14:O15' => [['2', ''], '加试'],
            'L16:O16' => [['2', ''], $stu['scoure_add']],
            'P14:S15' => [['2', ''], '加试'],
            'P16:S16' => [['2', ''], ''],
            'T14:T15' => [['1', ''], '总分'],
            'T16' => [['1', ''], $stu['scoure_sum']],
            'A17:E17' => [['1', ''], '学  制'],
            'F17:T17' => [['2', ''], $stu['student_last_type_dict']],
            'A18:E18' => [['1', ''], '入学年月日'],
            'F18:T18' => [['2', ''], ''],
            'A19:E19' => [['1', ''], '毕业年月日'],
            'F19:T19' => [['2', ''], ''],
            'A20:T23' => [['4', ''], '学籍变动记录：'],
            'A24:T25' => [['4', ''], '备注：'],

            //右半边
            'E9:N9' => [['2', ''], $stu['student_mail_address']],
            'O9:S9' => [['2', ''], '邮 编'],
            'T9' => [['2', ''], $stu['student_postcode']],
            'A10:D10' => [['2', ''], '联系电话'],
            'E10:T10' => [['2', ''], $stu['student_tel']],
            'A11:A13' => [['2', 'warpText'], '入学前情况'],
            'B11:D11' => [['2', ''], '最后学历'],
            'E11:H11' => [['2', ''], '内容111'],
            'I11:N11' => [['2', ''], '校名'],
            'O11:T11' => [['2', ''], '校名'],
            'B11:D11' => [['2', ''], '最后学历'],
            'B11:D11' => [['2', ''], '最后学历'],

        ];
        //每学期的标题
        for ($j = 6; $j <= 20; $j += 7) {
            $titleNum = $j - 1;
            $list = [
                'U' . $titleNum . ':Y' . $titleNum => [['2', ''], '20  ~20  学年第 一 学期'],
                'U' . $j => [['2', ''], '课 程 名 称'],
                'V' . $j => [['2', ''], '学分'],
                'W' . $j => [['2', ''], '成绩'],
                'X' . $j => [['2', ''], '补考'],
                'Y' . $j => [['2', ''], '重修']

            ];
            $exportData += $list; //添加数组
        }
        for ($j = 6; $j <= 20; $j += 7) {
            $titleNum = $j - 1;
            $list = [
                'Z' . $titleNum . ':AD' . $titleNum => [['2', ''], '20  ~20  学年第 二  学期'],
                'Z' . $j => [['2', ''], '课 程 名 称'],
                'AA' . $j => [['2', ''], '学分'],
                'AB' . $j => [['2', ''], '成绩'],
                'AC' . $j => [['2', ''], '补考'],
                'AD' . $j => [['2', ''], '重修']
            ];
            $exportData += $list; //添加数组
        }

        //每学期的成绩课程,按照列来弄，第一学期
        for ($i = 1; $i <= 5; $i++) {
            for ($j = 6 + $i; $j <= 25; $j += 7) {
                $list = [ //画一行
                    'U' . $j => [['5', ''], ' ' ],
                    // 'U' . $j => [['5', ''], ' ' . $i . '.'],
                    'V' . $j => [['2', ''], ' '],
                    'W' . $j => [['2', ''], ' '],
                    'X' . $j => [['2', ''], ' '],
                    'Y' . $j => [['2', ''], ' '],

                ];
                $exportData += $list; //添加数组
            }
        }
        //每学期的成绩课程,按照列来弄，第二学期
        for ($i = 1; $i <= 5; $i++) {
            for ($j = 6 + $i; $j <= 25; $j += 7) {
                $list = [ //画一行
                    'Z' . $j => [['5', ''], ' ' ],
                    'AA' . $j => [['2', ''], ' '],
                    'AB' . $j => [['2', ''], ' '],
                    'AC' . $j => [['2', ''], ' '],
                    'AD' . $j => [['2', ''], ' '],

                ];
                $exportData += $list; //添加数组
            }
        }

        $this->exportStyle($exportData, $sheet);




        //添加反面
        $sheet2 = $spreadsheet->createSheet(2)->setTitle('背面');
        $this->setColumn2($sheet2);
        $exportSheet2 = [
            'C1:G1' => [['6', ''], '学号：'],
            'A2:H2' => [['1', ''], '20  ~20  学年第 一 学期'],
            'I2:P2' => [['1', ''], '20  ~20  学年第 二 学期'],
            'A3:B5' => [['2', ''], '课 程 名 称'],
            'C3:C5' => [['2', ''], '学分'],
            'D3:E5' => [['2', ''], '成绩'],
            'F3:G5' => [['2', ''], '补考'],
            'H3:H5' => [['2', ''], '重修'],

            'I3:J5' => [['2', ''], '课 程 名 称'],
            'K3:K5' => [['2', ''], '学分'],
            'L3:M5' => [['2', ''], '成绩'],
            'N3:O5' => [['2', ''], '补考'],
            'P3:P5' => [['2', ''], '重修'],

            'Q2:S2' => [['7', ''], '毕 业 设 计'],
            'Q3:S5' => [['7', ''], '论文题目：'],
            'Q6:S6' => [['7', ''], '毕业答辩成绩：'],
            'Q7:S8' => [['7', ''], '指导教师：'],
            'Q9:S10' => [['7', ''], '答辩委员会主任（签字） '],

            'A11:H11' => [['1', ''], '20  ~20  学年第 三 学期'],
            'I11:P11' => [['1', ''], '20  ~20  学年第 四 学期'],
            'A12:A13' => [['2', 'warpText'], '实 训 内 容'],
            'B12:B13' => [['2', 'warpText'], '实训单位'],
            'C12:D13' => [['2', 'warpText'], '指导教师'],
            'E12:F13' => [['2', 'warpText'], '起止时间'],
            'G12:H13' => [['2', 'warpText'], '成绩'],

            'I12:I13' => [['2', 'warpText'], '实 训 内 容'],
            'J12:J13' => [['2', 'warpText'], '实训单位'],
            'K12:L13' => [['2', 'warpText'], '指导教师'],
            'M12:N13' => [['2', 'warpText'], '起止时间'],
            'O12:P13' => [['2', 'warpText'], '成绩'],

            'Q11:S11' => [['2', ''], '毕 业 前 补 考'],
            'Q12:Q13' => [['2', ''], '学 期'],
            'R12:R13' => [['2', 'warpText'], '课 程 名 称'],
            'S12:S13' => [['2', 'warpText'], '补 考 成 绩'],
            'Q18:S20' => [['4', 'warpText'], '备注：'],
            'A19' => [['1', ''], '是否毕业'],
            'B19' => [['2', ''], ''],
            'C19:H19' => [['1', ''], '毕业证书编号:'],
            'I19:P19' => [['2', ''], ''],

            'A20' => [['2', ''], '是否结业'],
            'B20' => [['2', ''], ''],
            'C20:H20' => [['2', ''], '学位证书编号:'],
            'I20:P20' => [['2', ''], ''],

            'A21:I21' => [['6', ''], '注：加粗字体为必填内容'],
        ];
        //第一列成绩
        $temp = 1;
        for ($j = 6; $j <= 10; $j += 1) {
            $list = [
                'A' . $j . ':B' . $j => [['5', ''],  ' '],
                'C' . $j => [['2', ''], ' '],
                'D' . $j . ':E' . $j => [['2', ''], ' '],
                'F' . $j . ':G' . $j => [['2', ''], ' '],
                'H' . $j => [['2', ''], ' ']

            ];
            $exportSheet2 += $list; //添加数组
            $temp++;
        }
        //第二列成绩
        $temp = 1;
        for ($j = 6; $j <= 10; $j += 1) {
            $list = [
                'I' . $j . ':J' . $j => [['5', ''],   ' '],
                'K' . $j => [['2', ''], ' '],
                'L' . $j . ':M' . $j => [['2', ''], ' '],
                'N' . $j . ':O' . $j => [['2', ''], ' '],
                'P' . $j => [['2', ''], ' ']

            ];
            $exportSheet2 += $list; //添加数组
            $temp++;
        }
        //第三、四学期
        $temp = 1;
        for ($j = 14; $j <= 18; $j += 1) {
            $list = [
                'A' . $j  => [['5', ''],   ' '],
                'B' . $j => [['2', ''], ' '],
                'C' . $j . ':D' . $j => [['2', ''], ' '],
                'E' . $j . ':F' . $j => [['2', ''], ' '],
                'G' . $j . ':H' . $j => [['2', ''], ' ']
            ];
            $exportSheet2 += $list; //添加数组
            $temp++;
        }
        //第二列成绩
        $temp = 1;
        for ($j = 14; $j <= 18; $j += 1) {
            $list = [
                'I' . $j  => [['5', ''],    ' '],
                'J' . $j => [['2', ''], ' '],
                'K' . $j . ':L' . $j => [['2', ''], ' '],
                'M' . $j . ':N' . $j => [['2', ''], ' '],
                'O' . $j . ':P' . $j => [['2', ''], ' ']

            ];
            $exportSheet2 += $list; //添加数组
            $temp++;
        }

        //补齐黑框
        for ($j = 14; $j <= 17; $j += 1) {
            $list = [
                'Q' . $j  => [['2', ''],  ' '],
                'R' . $j  => [['2', ''],  ' '],
                'S' . $j  => [['2', ''],  ' '],
            ];
            $exportSheet2 += $list; //添加数组
        }

        $this->exportStyle($exportSheet2, $sheet2);


        $file_name = $student_number . '-' . $name;

        if ($type == 'more') {
            $tempname = $file_name;
            // //第一种保存方式，保存在服务器
            $writer = IOFactory::createWriter($spreadsheet, 'Xls');
            //保存的路径可自行设置
            $path = __DIR__;
            $path = $_SERVER['DOCUMENT_ROOT'];
            $file_name = './exportfile/' . $file_name . '.xls';
            $writer->save($file_name);
            return $tempname . '.xls';
        } else {
            //第二种直接页面上显示下载
            $file_name = $file_name . ".xls";
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' . $file_name . '"');
            header('Cache-Control: max-age=0'); //禁止缓存
            $writer = IOFactory::createWriter($spreadsheet, 'Xls'); // 第二个参数首字母必须大写
            $writer->save('php://output');
            exit;
            return null;
        }
    }

    function exportStyle($exportData = [], $sheet)
    {
        //正文加粗字体
        $font1 =  [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER, //水平居中
                'vertical' => Alignment::VERTICAL_CENTER, //上下居中
            ],
            'font' => [
                'name' => '仿宋_GB2312',
                'bold' => true,
                'size' => 10.5,
            ],
            'borders' => [ //设置边框
                'outline' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => '000000']
                ],
            ]
        ];
        //正文普通字体
        $font2 =  [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER, //水平居中
                'vertical' => Alignment::VERTICAL_CENTER, //上下居中
            ],
            'font' => [
                'name' => '仿宋_GB2312',
                'size' => 10.5,
            ],
            'borders' => [ //设置边框
                'outline' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => '000000']
                ],
            ]
        ];

        //正文普通小字体
        $font3 =  [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER, //水平居中
                'vertical' => Alignment::VERTICAL_CENTER, //上下居中
            ],
            'font' => [
                'name' => '仿宋_GB2312',
                'size' => 9,
            ],
            'borders' => [ //设置边框
                'outline' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => '000000']
                ],
            ]
        ];

        //居左上角
        $font4 =  [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_LEFT, //水平居左
                'vertical' => Alignment::VERTICAL_TOP, //上下居上
            ],
            'font' => [
                'name' => '仿宋_GB2312',
                'size' => 9,
            ],
            'borders' => [ //设置边框
                'outline' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => '000000']
                ],
            ]
        ];
        $font5 =  [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_LEFT, //水平居左
                'vertical' => Alignment::VERTICAL_CENTER, //上下居中
            ],
            'font' => [
                'name' => '仿宋_GB2312',
                'size' => 9,
            ],
            'borders' => [ //设置边框
                'outline' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => '000000']
                ],
            ]
        ];
        //无边框
        $font6 =  [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER, //水平居中
                'vertical' => Alignment::VERTICAL_CENTER, //上下居中
            ],
            'font' => [
                'name' => '仿宋_GB2312',
                'bold' => true,
                'size' => 10.5,
            ]
        ];
        $font7 =  [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_LEFT, //水平居左
                'vertical' => Alignment::VERTICAL_CENTER, //上下居中
            ],
            'font' => [
                'name' => '仿宋_GB2312',
                'bold' => true,
                'size' => 9,
            ],
            'borders' => [ //设置边框
                'outline' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => '000000']
                ],
            ]
        ];
        foreach ($exportData as $key => $value) {
            $arr = explode(':', $key);
            if (count($arr) == 2) { //融合多單元格
                $sheet->mergeCells($key); //跟据key融合
                $sheet->setCellValue($arr[0], $value[1]); //設置值
            } else { //单个单元格

                $sheet->setCellValue($key, $value[1]); //設置值
            }

            switch ($value[0][0]) { //設置樣式
                case '1':
                    $sheet->getStyle($key)->applyFromArray($font1);
                    break;
                case '2':
                    $sheet->getStyle($key)->applyFromArray($font2);
                    break;
                case '3':
                    $sheet->getStyle($key)->applyFromArray($font3);
                    break;
                case '4':
                    $sheet->getStyle($key)->applyFromArray($font4);
                    break;
                case '5':
                    $sheet->getStyle($key)->applyFromArray($font5);
                    break;
                case '6':
                    $sheet->getStyle($key)->applyFromArray($font6);
                    break;
                case '7':
                    $sheet->getStyle($key)->applyFromArray($font7);
                    break;
                default:
                    $sheet->getStyle($key)->applyFromArray($font2);
                    break;
            }
            if ($value[0][1] == 'warpText') {
                $sheet->getStyle($key)->getAlignment()->setWrapText(true); //自动换行
            }
        }
    }

    function setColumn($sheet)
    {
        //外围边框
        $sheet->getStyle('A5:AD25')->applyFromArray([
            'borders' => [ //设置边框
                'outline' => [
                    'borderStyle' => Border::BORDER_MEDIUM, //加粗
                    'color' => ['argb' => '000000']
                ],
            ],

        ]);

        //每列单元格的宽//自动计算列宽$worksheet->getColumnDimension('A')->setAutoSize(true);
        $sheet->getColumnDimension('A')->setWidth(3.5);
        $sheet->getColumnDimension('B')->setWidth(4);
        $sheet->getColumnDimension('C')->setWidth(4);
        $sheet->getColumnDimension('D')->setWidth(2);
        $sheet->getColumnDimension('E')->setWidth(3);
        $sheet->getColumnDimension('F')->setWidth(2);
        $sheet->getColumnDimension('G')->setWidth(3);
        $sheet->getColumnDimension('H')->setWidth(2);
        $sheet->getColumnDimension('I')->setWidth(2);
        $sheet->getColumnDimension('J')->setWidth(2);
        $sheet->getColumnDimension('K')->setWidth(1);
        $sheet->getColumnDimension('L')->setWidth(1);
        $sheet->getColumnDimension('M')->setWidth(1);
        $sheet->getColumnDimension('N')->setWidth(4);
        $sheet->getColumnDimension('O')->setWidth(2);
        $sheet->getColumnDimension('P')->setWidth(1);
        $sheet->getColumnDimension('Q')->setWidth(2);
        $sheet->getColumnDimension('R')->setWidth(2);
        $sheet->getColumnDimension('S')->setWidth(2);
        $sheet->getColumnDimension('T')->setWidth(9);
        $sheet->getColumnDimension('U')->setWidth(27);
        $sheet->getColumnDimension('V')->setWidth(5);
        $sheet->getColumnDimension('W')->setWidth(5);
        $sheet->getColumnDimension('X')->setWidth(5);
        $sheet->getColumnDimension('Y')->setWidth(5);
        $sheet->getColumnDimension('Z')->setWidth(27);
        $sheet->getColumnDimension('AA')->setWidth(5);
        $sheet->getColumnDimension('AB')->setWidth(5);
        $sheet->getColumnDimension('AC')->setWidth(5);
        $sheet->getColumnDimension('AD')->setWidth(5);
        //每行单元格的高

        for ($x = 5; $x <= 25; $x++) {
            $sheet->getRowDimension($x)->setRowHeight(20);
        }

        //右下角落款
        $sheet->mergeCells('Z26:AD26');
        // $sheet->setCellValueByColumnAndRow(1, 2, '网非教学管理平台  '); //大单元格第1列，第二行设置内容

    }
    function setColumn2($sheet)
    {
        //外围边框
        $sheet->getStyle('A2:S20')->applyFromArray([
            'borders' => [ //设置边框
                'outline' => [
                    'borderStyle' => Border::BORDER_MEDIUM,
                    'color' => ['argb' => '000000']
                ],
            ],

        ]);

        //每列单元格的宽//自动计算列宽$worksheet->getColumnDimension('A')->setAutoSize(true);
        // $sheet->getColumnDimension('A')->setWidth(3.5);
        // $sheet->getColumnDimension('B')->setWidth(4);
        // $sheet->getColumnDimension('C')->setWidth(4);
        $sheet->getColumnDimension('D')->setWidth(4);
        $sheet->getColumnDimension('E')->setWidth(4);
        $sheet->getColumnDimension('F')->setWidth(4);
        $sheet->getColumnDimension('G')->setWidth(4);
        // $sheet->getColumnDimension('H')->setWidth(2);
        // $sheet->getColumnDimension('I')->setWidth(2);
        // $sheet->getColumnDimension('J')->setWidth(2);
        // $sheet->getColumnDimension('K')->setWidth(1);
        $sheet->getColumnDimension('L')->setWidth(4);
        $sheet->getColumnDimension('M')->setWidth(4);
        $sheet->getColumnDimension('N')->setWidth(4);
        $sheet->getColumnDimension('O')->setWidth(4);
        // $sheet->getColumnDimension('P')->setWidth(1);
        // $sheet->getColumnDimension('Q')->setWidth(2);
        // $sheet->getColumnDimension('R')->setWidth(2);
        // $sheet->getColumnDimension('S')->setWidth(2);
        // $sheet->getColumnDimension('T')->setWidth(9);
        // $sheet->getColumnDimension('U')->setWidth(27);
        // $sheet->getColumnDimension('V')->setWidth(5);
        // $sheet->getColumnDimension('W')->setWidth(5);
        // $sheet->getColumnDimension('X')->setWidth(5);
        // $sheet->getColumnDimension('Y')->setWidth(5);
        // $sheet->getColumnDimension('Z')->setWidth(27);
        // $sheet->getColumnDimension('AA')->setWidth(5);
        // $sheet->getColumnDimension('AB')->setWidth(5);
        // $sheet->getColumnDimension('AC')->setWidth(5);
        // $sheet->getColumnDimension('AD')->setWidth(5);
        //每行单元格的高

        for ($x = 5; $x <= 25; $x++) {
            $sheet->getRowDimension($x)->setRowHeight(28);
        }
    }
}
